package org.jing.ext.office;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jing.core.lang.JingException;
import org.jing.core.lang.itf.JEnum;
import org.jing.core.logger.JingLogger;
import org.jing.core.util.StringUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;

/**
 * Description: <br>
 *
 * @author bks <br>
 * @since 2020-06-02 <br>
 */
@SuppressWarnings("Duplicates") public class ExcelOperator {
    private static final JingLogger LOGGER = JingLogger.getLogger(ExcelOperator.class);

    public enum Suffix implements JEnum<String> {
        XLS(".xls"), XLSX(".xlsx"), ET(".et");

        private final String suffix;

        Suffix(String suffix) {
            this.suffix = suffix;
        }

        public String getValue() {
            return suffix;
        }

        public static Suffix getSuffix(String suffix) {
            if (!suffix.startsWith(".")) {
                suffix = "." + suffix;
            }
            Suffix[] suffixes = values();
            for (Suffix s : suffixes) {
                if (s.suffix.equalsIgnoreCase(suffix)) {
                    return s;
                }
            }
            return null;
        }

        @Override public boolean equalsByType(Object... objects) {
            String suffix = (String) objects[0];
            if (!suffix.startsWith(".")) {
                suffix = "." + suffix;
            }
            return this.suffix.equals(suffix);
        }

        @Override public String toString() {
            return suffix;
        }
    }

    public static class CellStyle {
        private org.apache.poi.ss.usermodel.CellStyle cellStyle;

        private Workbook excel;

        private boolean fontFlag = false;

        private CellStyle(Workbook excel) {
            this.excel = excel;
            this.cellStyle = excel.createCellStyle();
        }

        public Cell add2Cell(org.apache.poi.ss.usermodel.Cell cell) {
            return new Cell(cell).setCellStyle(cellStyle);
        }

        public CellStyle setCellFormat(short dataFormat) {
            cellStyle.setDataFormat(dataFormat);
            return this;
        }

        // top, bottom, left, right.
        public CellStyle setBorder(BorderStyle... styles) {
            if (null == styles || styles.length == 0) {
                return this;
            }
            cellStyle.setBorderTop(styles[0]);
            if (styles.length > 1) {
                cellStyle.setBorderBottom(styles[1]);
            }
            if (styles.length > 2) {
                cellStyle.setBorderLeft(styles[2]);
            }
            if (styles.length > 3) {
                cellStyle.setBorderRight(styles[3]);
            }
            return this;
        }

        public CellStyle setAllBorder(BorderStyle style) {
            cellStyle.setBorderTop(style);
            cellStyle.setBorderBottom(style);
            cellStyle.setBorderLeft(style);
            cellStyle.setBorderRight(style);
            return this;
        }

        public CellStyle setTopBorder(BorderStyle style) {
            cellStyle.setBorderTop(style);
            return this;
        }

        public CellStyle setBottomBorder(BorderStyle style) {
            cellStyle.setBorderBottom(style);
            return this;
        }

        public CellStyle setLeftBorder(BorderStyle style) {
            cellStyle.setBorderLeft(style);
            return this;
        }

        public CellStyle setRightBorder(BorderStyle style) {
            cellStyle.setBorderRight(style);
            return this;
        }

        public CellStyle setFontNameAndSize(String fontName, int size) {
            if (fontFlag && cellStyle instanceof HSSFCellStyle) {
                ((HSSFCellStyle) cellStyle).getFont(excel).setFontName(fontName);
                ((HSSFCellStyle) cellStyle).getFont(excel).setFontHeightInPoints((short) size);
            }
            else if (fontFlag && cellStyle instanceof XSSFCellStyle) {
                ((XSSFCellStyle) cellStyle).getFont().setFontName(fontName);
                ((XSSFCellStyle) cellStyle).getFont().setFontHeightInPoints((short) size);
            }
            else {
                Font font = excel.createFont();
                font.setFontName(fontName);
                font.setFontHeightInPoints((short) size);
                cellStyle.setFont(font);
            }
            fontFlag = true;
            return this;
        }

        public CellStyle setFontNameAndSize(FontName fontName, int size) {
            if (fontFlag && cellStyle instanceof HSSFCellStyle) {
                ((HSSFCellStyle) cellStyle).getFont(excel).setFontName(fontName.getValue());
                ((HSSFCellStyle) cellStyle).getFont(excel).setFontHeightInPoints((short) size);
            }
            else if (fontFlag && cellStyle instanceof XSSFCellStyle) {
                ((XSSFCellStyle) cellStyle).getFont().setFontName(fontName.getValue());
                ((XSSFCellStyle) cellStyle).getFont().setFontHeightInPoints((short) size);
            }
            else {
                Font font = excel.createFont();
                font.setFontName(fontName.getValue());
                font.setFontHeightInPoints((short) size);
                cellStyle.setFont(font);
            }
            fontFlag = true;
            return this;
        }

        public CellStyle setAlignment(HorizontalAlignment alignment, VerticalAlignment verticalAlignment) {
            cellStyle.setAlignment(alignment);
            cellStyle.setVerticalAlignment(verticalAlignment);
            return this;
        }

        public CellStyle setWrap(boolean wrap) {
            cellStyle.setWrapText(wrap);
            return this;
        }

        public CellStyle setBold(boolean bold) {
            if (fontFlag && cellStyle instanceof HSSFCellStyle) {
                ((HSSFCellStyle) cellStyle).getFont(excel).setBold(bold);
            }
            else if (fontFlag && cellStyle instanceof XSSFCellStyle) {
                ((XSSFCellStyle) cellStyle).getFont().setBold(bold);
            }
            else {
                Font font = excel.createFont();
                font.setBold(bold);
                cellStyle.setFont(font);
            }
            fontFlag = true;
            return this;
        }

        public org.apache.poi.ss.usermodel.CellStyle getBase() {
            return cellStyle;
        }
    }

    public static class Cell {
        private org.apache.poi.ss.usermodel.Cell cell;

        public Cell(org.apache.poi.ss.usermodel.Cell cell) {
            this.cell = cell;
        }

        public Cell setCellStyle(org.apache.poi.ss.usermodel.CellStyle cellStyle) {
            cell.setCellStyle(cellStyle);
            return this;
        }

        public org.apache.poi.ss.usermodel.Cell getBase() {
            return cell;
        }
    }

    public static Workbook readExcel(InputStream is, Suffix suffix) {
        LOGGER.debug("Reading excel with InputStream... [suffix: {}]", suffix);
        try {
            switch (suffix) {
                case XLS:
                case ET:
                    return new HSSFWorkbook(is);
                case XLSX:
                    return new XSSFWorkbook(is);
                default:
                    throw new JingException("Invalid excel suffix: {}", suffix.getValue());
            }
        }
        catch (JingException e) {
            throw e;
        }
        catch (Throwable t) {
            throw new JingException(t, "Failed to get workbook by suffix: {}", suffix.getValue());
        }
    }

    public static Workbook readExcel(File file) {
        LOGGER.debug("Reading excel with file... [file: {}]", file.getName());
        String suffix;
        try {
            suffix = file.getName().substring(file.getName().lastIndexOf("."));
        }
        catch (JingException e) {
            throw e;
        }
        catch (Throwable t) {
            throw new JingException(t, "Invalid file suffix: {}", file.getName());
        }
        if (StringUtil.isEmpty(suffix)) {
            throw new JingException("Invalid file suffix: {}", file.getName());
        }
        try (InputStream is = new FileInputStream(file)){
            return readExcel(is, Suffix.getSuffix(suffix));
        }
        catch (JingException e) {
            throw e;
        }
        catch (Throwable t) {
            throw new JingException(t, "Failed to read workbook: {}", file.getAbsolutePath());
        }
    }

    public static Workbook createExcel(File file) {
        LOGGER.debug("Creating excel with file... [file: {}]", file.getName());
        String suffixStr;
        try {
            suffixStr = file.getName().substring(file.getName().lastIndexOf("."));
        }
        catch (JingException e) {
            throw e;
        }
        catch (Throwable t) {
            throw new JingException(t, "Invalid file suffix: {}", file.getName());
        }
        if (StringUtil.isEmpty(suffixStr)) {
            throw new JingException("Invalid file suffix: {}", file.getName());
        }

        try {
            Suffix suffix = Suffix.getSuffix(suffixStr);
            if (null == suffix) {
                throw new JingException(StringUtil.mixParameters("Invalid excel suffix: {}", suffixStr));
            }
            switch (suffix) {
                case XLS:
                case ET:
                    return new HSSFWorkbook();
                case XLSX:
                    return new XSSFWorkbook();
                default:
                    throw new JingException("Invalid excel suffix: {}", suffixStr);
            }
        }
        catch (JingException e) {
            throw e;
        }
        catch (Throwable t) {
            throw new JingException(t, "Failed to get workbook by suffix: {}", suffixStr);
        }
    }

    public static void writeExcel(Workbook excel, File file) {
        LOGGER.debug("Writing excel to file... [file: {}]", file.getName());
        try (OutputStream os = new FileOutputStream(file)){
            excel.write(os);
            excel.close();
        }
        catch (JingException e) {
            throw e;
        }
        catch (Throwable t) {
            throw new JingException(t, "Failed to write workbook: {}", file.getAbsolutePath());
        }
    }

    public static CellStyle createCellStyle(Workbook excel) {
        return new CellStyle(excel);
    }

    public static short createDataFormat(Workbook excel, String formatString) {
        if (excel instanceof HSSFWorkbook) {
            HSSFDataFormat dataFormat = ((HSSFWorkbook) excel).createDataFormat();
            return dataFormat.getFormat(formatString);
        }
        else if (excel instanceof XSSFWorkbook) {
            XSSFDataFormat dataFormat = ((XSSFWorkbook) excel).createDataFormat();
            return dataFormat.getFormat(formatString);
        }
        else {
            throw new JingException("Invalid workbook type.");
        }
    }

    public static void mergeCell(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol,
        BorderStyle... styles) {
        CellRangeAddress rangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(rangeAddress);
        if (null == styles || styles.length == 0) {
            return;
        }
        RegionUtil.setBorderTop(styles[0], rangeAddress, sheet);
        if (styles.length > 1) {
            RegionUtil.setBorderBottom(styles[1], rangeAddress, sheet);
        }
        if (styles.length > 2) {
            RegionUtil.setBorderLeft(styles[2], rangeAddress, sheet);
        }
        if (styles.length > 3) {
            RegionUtil.setBorderRight(styles[3], rangeAddress, sheet);
        }
    }

    public static String getCellString(org.apache.poi.ss.usermodel.Cell cell) {
        if (null == cell) {
            return "";
        }
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                double value = cell.getNumericCellValue();
                if (value % 1.0 != 0) {
                    return String.valueOf(value);
                }
                else {
                    return String.valueOf((long) value);
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case BLANK:
                return "";
            default:
                throw new JingException(
                    "Cannot transfer Cell to String with CellType: " + cell.getCellTypeEnum().toString());
        }
    }
}
